﻿Public Class Clsdatabase

    Public Shared Function findrow(ByVal datatable As System.Data.DataTable, ByVal colonna As System.Data.DataColumn, ByVal ricerca As String)
        Dim riga As Integer = Nothing
        Dim arrayriga As New ArrayList
        For i = 0 To datatable.Rows.Count - 1
            Try
                If datatable.Rows(i).Item(colonna) = ricerca Then
                    'riga = i
                    arrayriga.Add(i)
                    'Exit For
                End If
            Catch ex As Exception
            End Try

        Next

        Return arrayriga

    End Function

    Public Shared Function CARICA_DATI(ByVal DATATABLE As System.Data.DataTable, ByVal COLONNA As System.Data.DataColumn)
        Dim DATI As New System.Collections.ObjectModel.Collection(Of String)
        Dim cella As String

        For i = 0 To DATATABLE.Rows.Count - 1
            Try
                If DATATABLE.Rows(i).RowState = Data.DataRowState.Deleted Then
                Else
                    If IsDBNull(DATATABLE.Rows(i).Item(COLONNA)) = False Then
                        cella = DATATABLE.Rows(i).Item(COLONNA)
                        If DATI.Contains(cella) Then
                        Else
                            DATI.Add(cella)
                        End If
                    End If
                End If

            Catch ex As Exception

            End Try


        Next

        Return DATI

    End Function

    Public Shared Function cerca(ByVal colonna As String, ByVal parametro As String, ByVal insieme As System.Data.DataTable)

        Dim righe As System.Data.DataRow = Nothing
        'Dim datatable As New System.Data.DataTable
        'datatable = insieme

        For i = 0 To insieme.Rows.Count - 1
            Try
                If insieme.Rows(i).RowState = Data.DataRowState.Deleted Then
                Else
                    If insieme.Rows(i).Item(colonna) = parametro Then
                    Else
                        insieme.Rows(i).Delete()
                    End If
                End If

            Catch ex As Exception
            End Try
        Next
        Return insieme
    End Function
    
    Public Shared Sub elimina_riga(ByVal tabella As Data.DataTable, ByVal nriga As Integer)
        tabella.Rows(nriga).Delete()
    End Sub

    ''' <summary>
    ''' Funzione per ricavare una lista dei campi contenuti in una certa colonna
    ''' </summary>
    ''' <param name="tabella">String - Nome della tabella dove effettuare la ricerca</param>
    ''' <param name="colonna">String - Nome della colonna</param>
    ''' <returns>Arraylist</returns>
    ''' <remarks></remarks>
    Public Shared Function Lista_item(ByVal tabella As String, ByVal colonna As String) As ArrayList

        Dim LISTAS As New ArrayList
        Dim DA As System.Data.OleDb.OleDbDataAdapter
        Dim query As New Data.OleDb.OleDbCommand
        Dim CONNESSIONE As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Lafer2k3\uff_tec\Dbporteclienti.mdb")
        Dim dataset As New System.Data.DataSet

        query.CommandText = ("SELECT " & colonna & " FROM " & tabella)
        DA = New Data.OleDb.OleDbDataAdapter(query.ToString, CONNESSIONE)
        DA.SelectCommand = query
        DA.SelectCommand.Connection = CONNESSIONE
        dataset.Tables.Add(tabella)
        DA.Fill(dataset, tabella)
        CONNESSIONE.Close()

        Dim numero_righe = dataset.Tables(0).Rows.Count

        For i = 0 To numero_righe - 1
            Dim campo = dataset.Tables(0).Rows(i).Item(0).ToString
            If LISTAS.Contains(campo) Then
            Else
                LISTAS.Add(campo)
            End If
        Next

        Return LISTAS
    End Function

    ''' <summary>
    ''' Sub per inserimento riga nel database
    ''' </summary>
    ''' <param name="query_inserimento">String - Query SQL completa per inserimento in tabella</param>
    ''' <remarks></remarks>
    Public Shared Sub inserisci_riga_gen(ByVal query_inserimento As String)

        Dim DA As System.Data.OleDb.OleDbDataAdapter
        Dim query As New Data.OleDb.OleDbCommand
        Dim CONNESSIONE As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Lafer2k3\uff_tec\Dbporteclienti.mdb")
        query.CommandText = query_inserimento
        DA = New Data.OleDb.OleDbDataAdapter(query.ToString, CONNESSIONE)
        DA.InsertCommand = query
        DA.InsertCommand.Connection = CONNESSIONE
        CONNESSIONE.Open()
        DA.InsertCommand.ExecuteNonQuery()
        CONNESSIONE.Close()

    End Sub

    ''' <summary>
    ''' Funzione per ricavare dataset contenente le righe che contengoni i parametri impostati
    ''' </summary>
    ''' <param name="TABELLA">String - Nome della tabella dove effettuare la ricerca</param>
    ''' <param name="COLONNA">String - Nome della colonna</param>
    ''' <param name="CAMPO"> String - Contenuto del campo se stringa in formato 'campo'</param>
    ''' <param name="COLONNA1">String - Nome della colonna</param>
    ''' <param name="CAMPO1">String - Contenuto del campo se stringa in formato 'campo'</param>
    ''' <param name="COLONNA2">String - Nome della colonna</param>
    ''' <param name="CAMPO2">String - Contenuto del campo se stringa in formato 'campo'</param>
    ''' <param name="COLONNA3">String - Nome della colonna</param>
    ''' <param name="CAMPO3">String - Contenuto del campo se stringa in formato 'campo'</param>
    ''' <returns>Dataset</returns>
    ''' <remarks></remarks>
    Public Shared Function FIND_ROW_GEN(ByVal TABELLA As String, ByVal COLONNA As String, ByVal CAMPO As String, _
                                        Optional ByVal COLONNA1 As String = "", Optional ByVal CAMPO1 As String = "", _
                                        Optional ByVal COLONNA2 As String = "", Optional ByVal CAMPO2 As String = "", _
                                        Optional ByVal COLONNA3 As String = "", Optional ByVal CAMPO3 As String = "") As System.Data.DataSet

        Dim DA As System.Data.OleDb.OleDbDataAdapter
        Dim query As New Data.OleDb.OleDbCommand
        Dim CONNESSIONE As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Lafer2k3\uff_tec\Dbporteclienti.mdb")
        Dim DATASET As New System.Data.DataSet

        If CAMPO3 = "''" Then
            CAMPO3 = ""
        End If

        Dim ST As New System.Text.StringBuilder

        ST.Append("SELECT * FROM " & TABELLA)

        If CAMPO <> "" Then
            ST.Append(" WHERE " & COLONNA & "=" & CAMPO & " AND")
        End If

        If CAMPO1 <> "" Then
            ST.Append(" WHERE " & COLONNA1 & "=" & CAMPO1 & " AND")
        End If

        If CAMPO2 <> "" Then
            ST.Append(" WHERE " & COLONNA2 & "=" & CAMPO2 & " AND")
        End If

        If CAMPO3 <> "" Then
            ST.Append(" WHERE " & COLONNA3 & "=" & CAMPO3 & " AND")
        End If

        If ST.ToString.EndsWith("AND") Then
            query.CommandText = ST.ToString.Remove(ST.ToString.Count - 4, 4)
        ElseIf ST.ToString.EndsWith(TABELLA) Then
            query.CommandText = (ST.ToString)
        End If


        DA = New Data.OleDb.OleDbDataAdapter(query.ToString, CONNESSIONE)
        DA.SelectCommand = query
        DATASET.Clear()
        DATASET.Tables.Add("QUANTITA")
        DA.SelectCommand.Connection = CONNESSIONE
        DA.Fill(DATASET, "QUANTITA")

        Return DATASET

    End Function
End Class
